# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/7/19 10:30'

from common.orm_helper import *
from common.excel_helper import ExcelReader
from k3_data_report.import_tb.utils import fetch_tb_unique_his_data
from  common.logger_helper import  g_wlogger
from common.utils import get_month

##########################################
# 3 导入k3基础表  采购订单  BEGIN
##########################################
class CaiGouList(Base):
    __tablename__ = "k3_caigoud"  # 自定义数据库表名称
    id = Column(Integer, primary_key=True)  # 自增
    sjsj = Column(String(10), verbose_name='年月')
    brand = Column(String(50), verbose_name='品牌')
    caigou_date = Column(String(50), verbose_name='采购日期')
    caigou_code = Column(String(50), verbose_name='采购订单号')
    version = Column(String(10), verbose_name='版本号')
    check_flag = Column(String(10), verbose_name='审核标记')
    close_flag = Column(String(10), verbose_name='关闭标记')
    wuliao_name = Column(String(150), verbose_name='物料名称')
    guige_xinhao = Column(String(100), verbose_name='规格型号')
    unit = Column(String(100), verbose_name='单位')
    bibie = Column(String(20), verbose_name='币别')
    huilv =  Column(String(20), verbose_name='汇率')
    dpartment = Column(String(20), verbose_name='部门')
    yewu_name = Column(String(20), verbose_name='业务员')
    number = Column(String(20), verbose_name='数量')
    jiaohuo_date = Column(String(20), verbose_name='交货日期')
    weishui_uprice = Column(String(20), verbose_name='未税单价')
    zhekou_rate =  Column(String(20), verbose_name='折扣率')
    shui_rate =  Column(String(20), verbose_name='税率')
    weishui_jine = Column(String(20), verbose_name='未税金额')
    qihuo_notes = Column(String(200), verbose_name='期货备注')
    shenhe_name = Column(String(20), verbose_name='审核人')
    jiben_unit =  Column(String(20), verbose_name='基本单位')
    jiben_number =  Column(String(20), verbose_name='基本单位数量')
    hy_close_flag = Column(String(20), verbose_name='行业务关闭标志')
    wl_longcode = Column(String(20), verbose_name='物料长代码')
    caigou_type = Column(String(20), verbose_name='采购方式')
    ruku_number = Column(String(20), verbose_name='入库数量')
    jiesuan_type = Column(String(50), verbose_name='结算方式')
    jiesuan_date = Column(String(20), verbose_name='结算日期')
    shenhe_date = Column(String(20), verbose_name='审核日期')
    hanshui_unit = Column(String(20), verbose_name='含税单价')
    shuie = Column(String(20), verbose_name='税额')
    jiashui_heji = Column(String(20), verbose_name='价税合计')
    kaipiao_number =  Column(String(20), verbose_name='开票数量')
    changyong_unit_nb = Column(String(20), verbose_name='常用单位数量')
    fukuan_guanlian_jine = Column(String(20), verbose_name='付款关联金额')
    kucun_number =  Column(String(20), verbose_name='库存数量')
    gongys_lianxi =  Column(String(50), verbose_name='供应商联系人')
    gongys_name  =  Column(String(200), verbose_name='供应商')


def caigou_process(excel_name, sheet_name):
    '''
    付款表处理过程
    :param excel_name:
    :param sheet_name:
    :return:
    '''
    ereader = ExcelReader(excel_name,sheet_name=sheet_name)
    headers = ereader.get_excel_headers()
    data_lists = ereader.get_excel_data_list()
    danju_date = data_lists[0][1]
    danju_month = "%s-%s" % (danju_date.split("-")[0], danju_date.split("-")[1])
    unique_select_sql = "select  distinct sjsj  from k3_caigoud"
    unique_set = fetch_tb_unique_his_data(unique_select_sql)
    if danju_month in unique_set:
        already_info = f"caigou_process month:{danju_month} already existed."
        g_wlogger.winfo(already_info)
        return
    for data_list in data_lists:
        caigou_code = data_list[2]
        if caigou_code == '':
            continue
        caigou_date = data_list[1]
        danju_month = get_month(caigou_date)
        caigoud = CaiGouList(brand=data_list[0], sjsj=danju_month, caigou_date=caigou_date,caigou_code=caigou_code,
                        version=data_list[3], check_flag=data_list[4], close_flag=data_list[5],
                        wuliao_name=data_list[6],  guige_xinhao=data_list[7], unit=data_list[8],
                        bibie=data_list[9], huilv=data_list[10],dpartment=data_list[11],yewu_name=data_list[12],
                        number=data_list[13], jiaohuo_date=data_list[14],weishui_uprice=data_list[15],
                        zhekou_rate=data_list[16],shui_rate=data_list[17], weishui_jine=data_list[18],
                        qihuo_notes = data_list[19],shenhe_name=data_list[20],jiben_unit=data_list[21],
                        jiben_number = data_list[22], hy_close_flag=data_list[24],
                        wl_longcode = data_list[25], caigou_type=data_list[26],ruku_number=data_list[27],
                        jiesuan_type = data_list[28], jiesuan_date=data_list[29],shenhe_date=data_list[31],
                        hanshui_unit=data_list[32], shuie=data_list[34], jiashui_heji=data_list[35],
                        kaipiao_number = data_list[37],changyong_unit_nb=data_list[39],fukuan_guanlian_jine=data_list[40],
                        kucun_number = data_list[42],gongys_lianxi=data_list[43], gongys_name=data_list[44])
        session.add(caigoud)
        session.commit()

    cgdd_succ = f"采购订单 文件：{excel_name}， 日期：{danju_month}处理完毕！"
    g_wlogger.winfo(cgdd_succ)


def caigou_process_main(excel_name, sheet_name):
    try:
        caigou_process(excel_name, sheet_name)
        return True
    except Exception as e:
        g_wlogger.werror(f"caigou_process_main has error:{e} with excel:{excel_name}")
        return False

##########################################
# 3 导入k3基础表  采购订单  END
##########################################



